package com.niannian.db;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import com.niannian.bean.ChatMsgBean;
import com.niannian.bean.FNCommentBean;
import com.niannian.bean.FamilyNewsBean;
import com.niannian.bean.GreetingNewsBean;
import com.niannian.bean.MyFamilyMemberBean;
import com.niannian.bean.NewsBean;
import com.niannian.bean.NewssBean;
import com.niannian.bean.NotificationBean;
import com.niannian.bean.NotifyDetailBean;
import com.niannian.bean.RegardsTipBean;
import com.niannian.bean.SystemNewsBean;
import com.niannian.util.Common;
import com.niannian.util.UpdateManager;
import android.content.Context;
import android.database.Cursor;
import android.view.View;
import android.widget.TextView;

/**
 * @author shengl
 * 
 */
public class DatabaseApi extends DatabaseAccess {
	private static DatabaseApi dbApi = null;
	public final int familynewsmaxitems = 10;
	public final int relationmaxitems = 10;
	public final int limitnum = 10;

	String[] familyNewsField = new String[] { "id", "family", "author", "author_nickname", "author_avatar", "info", "pic", "aud", "created_time", "aud_len",
			"pic_num" };
	String[] familyNewsCommentField = new String[] { "id", "author", "author_nickname", "author_avatar", "post", "info", "aud", "aud_len", "created_time",
			"reply_to", "reply_to_nickname" };
	String[] myFamilyField = new String[] { "id", "nickname", "family", "email", "avatar", "innername", "phone", "birth", "gender", "city", "info_completed" };

	String[] greetingField = new String[] { "id", "author", "author_nickname", "author_avatar", "author_innername", "info", "pic", "aud", "aud_len",
			"created_time", "type", "receiver", "receiver_innername", "receiver_nickname", "sent_time", "receiver_avatar", "is_checked" };

	String[] newssField = new String[] { "msg_count", "sender", "msg_type", "item_time", "item_content", "sender_avatar", "sender_nickname" };

	String[] systemNews = new String[] { "id", "title", "receiver", "desc", "type", "is_checked", "created_time" };

	String[] regardsTipField = new String[] { "id", "creator", "title", "spec_date", "notice_type", "desc", "is_triggered", "is_checked" };

	String[] notifyGetField = new String[] { "id", "fromuid", "fromuname", "msg", "type", "dateline" };
	String[] notifySaveField = new String[] { "hasuid", "nid", "targetid", "targetname", "msg", "type", "dateline" };

	String[] msgSaveField = new String[] { "msgid", "msg", "targetuid", "targetname", "dateline", "hasuid", "issend" };
	String[] msgGetField = new String[] { "id", "msg", "fromuid", "fromuname", "dateline" };

	String[] scheduleField = new String[] { "sid", "uid", "color", "name", "note", "startweek", "endweek", "teacher", "year", "term", "uniqueid", "share",
			"source" };

	String[] NewsField = new String[] { "id", "author_avatar", "author_nickname", "cmt_info", "cmt_aud", "cmt_aud_len", "post", "post_pic", "post_info",
			"created_time" };

	private DatabaseApi(Context mCtx) {
		super(mCtx);
	}

	public static DatabaseApi getDataBaseApi(Context mCtx) {
		if (dbApi == null) {
			dbApi = new DatabaseApi(mCtx);
		}
		return dbApi;
	}

	/*******************************************************
	 * 家庭动态start
	 ******************************************************/
	/**
	 * 增加家庭动态
	 * 
	 * @param uid
	 * @param familyNewslist
	 * @throws JSONException
	 */
	public void updateFamilyNews(int uid, JSONArray familyNewslist, int lasttime, String news_type) throws JSONException {
		open();

		String timeline = "0";
		if (lasttime == 0) {
			timeline = "" + ((System.currentTimeMillis()) / 1000);
			String del = "DELETE FROM family_news where created_time<'" + timeline + "'AND hasuid='" + uid + "'AND news_type='" + news_type + "'";
			mDb.execSQL(del);
		}

		for (int i = 0; i < familyNewslist.length(); i++) {
			JSONObject dataObj = familyNewslist.getJSONObject(i);
			String newsValueStr = "";
			boolean isAdd = true;
			for (String field : familyNewsField) {
				if (dataObj.has(field)) {
					String value = dataObj.getString(field);
					if (value != null) {
						if ("created_time".equals(field)) {
							value = Common.getChangeTime(value);
						}
						if (!"".equals(newsValueStr))
							newsValueStr += ",";
						newsValueStr += "'" + Common.formatSqlValue(value) + "'";
					} else {
						isAdd = false;
						break;
					}
				} else {
					isAdd = false;
					break;
				}
			}
			if (isAdd) {
				String insertFamilyNews = "REPLACE INTO family_news('hasuid','news_type'," + Common.sImplode(familyNewsField) + ") VALUES ('" + uid + "','"
						+ news_type + "'," + newsValueStr + ")";
				mDb.execSQL(insertFamilyNews);

				JSONArray commArray = dataObj.getJSONArray("comments");
				String delComment = "DELETE FROM family_news_comment WHERE hasuid='" + uid + "' AND post='" + dataObj.getInt("id") + "'";

				mDb.execSQL(delComment);
				if (!Common.empty(commArray)) {
					updateFNComment(uid, commArray, lasttime, false);
				}

			}
		}
		close();
	}

	/**
	 * 更新家庭动态评论
	 * 
	 * @param uid
	 * @param commArray
	 * @param lasttime
	 * @throws JSONException
	 */
	public void updateFNComment(int uid, JSONArray commArray, int lasttime, Boolean isopen) throws JSONException {
		try {

			if (isopen) {
				open();
			}
			for (int ci = 0; ci < commArray.length(); ci++) {
				JSONObject commObject = commArray.getJSONObject(ci);
				String commValueStr = "";
				boolean iscommAdd = true;
				for (String field : familyNewsCommentField) {
					if (commObject.has(field)) {
						String value = commObject.getString(field);
						if (value != null) {
							if ("created_time".equals(field)) {
								value = Common.getChangeTime(value);
							}
							if (!"".equals(commValueStr))
								commValueStr += ",";
							commValueStr += "'" + Common.formatSqlValue(value) + "'";
						} else {
							iscommAdd = false;
							break;
						}
					} else {
						iscommAdd = false;
						break;
					}
				}
				if (iscommAdd) {
					String insertComment = "REPLACE INTO family_news_comment('hasuid'," + Common.sImplode(familyNewsCommentField) + ") VALUES ('" + uid + "',"
							+ commValueStr + ")";
					mDb.execSQL(insertComment);
				}

			}
			if (isopen) {
				close();
			}

		} catch (Exception e) {
			e.getMessage();
		}
	}

	/**
	 * 加载家庭动态
	 * 
	 * @param uid
	 *            用户ID
	 * @param familyid
	 *            家庭ID
	 * @param dateline
	 *            时间段
	 * 
	 */
	public List<FamilyNewsBean> loadFamilyNews(int uid, int familyid, int dateline, String news_type) {

		try {
			String dateSql = "", sql = "SELECT * FROM family_news WHERE hasuid='" + uid + "'AND family='" + familyid + "'";
			if (dateline > 0) {
				dateSql = " AND created_time<'" + dateline + "'";
			}
			if (news_type.length() > 0) {
				sql = sql + " AND news_type='" + news_type + "'";
			}
			sql = sql + dateSql + "ORDER BY created_time DESC limit 0," + familynewsmaxitems;
			List<FamilyNewsBean> list = executeQuery(sql, FamilyNewsBean.class);
			for (int i = 0; i < list.size(); i++) {
				List<FNCommentBean> listcomment = loadFNComment(uid, list.get(i).getId(), dateline);
				if (!Common.empty(listcomment)) {
					list.get(i).setCommentBeans(listcomment);
				}
			}
			return list;

		} catch (Exception e) {
			return null;
			// TODO: handle exception
		}

	}

	/**
	 * 加载一个动态
	 * 
	 * @param uid
	 *            用户ID
	 * @param id
	 *            动态ID
	 */
	public FamilyNewsBean loadOneFamilyNews(int uid, int id) {
		String query = "SELECT * FROM family_news WHERE hasuid='" + uid + "' AND id='" + id + "' limit 0,1";
		List<FamilyNewsBean> fBeans = (List<FamilyNewsBean>) executeQuery(query, FamilyNewsBean.class);
		if (fBeans.size() > 0) {
			List<FNCommentBean> listcomment = loadFNComment(uid, fBeans.get(0).getId(), 0);
			if (!Common.empty(listcomment)) {
				fBeans.get(0).setCommentBeans(listcomment);
			}
			return fBeans.get(0);
		} else {
			return null;
		}
	}

	/**
	 * 加载家庭动态评论
	 * 
	 * @param uid
	 *            用户id
	 * @param post
	 *            家庭的动态id
	 * @param dateline
	 * @return
	 */
	public FNCommentBean loadOneFNComment(int uid, int post, int id) {

		try {

			String query = "SELECT * FROM family_news_comment WHERE hasuid='" + uid + "' AND id='" + id + "' AND post='" + post + "' limit 0,1";
			List<FNCommentBean> cBeans = (List<FNCommentBean>) executeQuery(query, FNCommentBean.class);
			if (cBeans.size() > 0) {
				return cBeans.get(0);
			} else {
				return null;
			}

		} catch (Exception e) {
			return null;
			// TODO: handle exception
		}

	}

	public List<FNCommentBean> loadFNComment(int uid, int post, int dateline) {

		try {

			String sql = "SELECT * FROM family_news_comment WHERE hasuid='" + uid + "'AND post='" + post + "'";
			List<FNCommentBean> list;
			list = executeQuery(sql, FNCommentBean.class);
			return list;

		} catch (Exception e) {
			return null;
			// TODO: handle exception
		}

	}

	/**
	 * 删除一个家庭动态
	 */
	public void deleteFamilyNews(int uid, int fid) {
		String sql = "DELETE FROM family_news WHERE hasuid='" + uid + "' AND family='" + fid + "'";
		open();
		mDb.execSQL(sql);
		close();
	}

	/**
	 * 删除一个家庭动态评论
	 */
	public void deleteFamilyNewsComment(int uid, int fid, int cid) {
		String sql = "DELETE FROM family_news_comment WHERE hasuid='" + uid + "' AND post='" + fid + "' AND id='" + cid + "'";
		open();
		mDb.execSQL(sql);
		close();
	}

	/*******************************************************
	 * 家庭动态end
	 ******************************************************/

	/*******************************************************
	 * 家庭成员管理start
	 ******************************************************/
	/**
	 * 更新家庭成员组信息
	 * 
	 * @param uid
	 * @param familyNewslist
	 * @throws JSONException
	 */
	public void updateMyFamily(int uid, JSONArray myFamilylist) throws JSONException {
		open();
		String del = "DELETE FROM my_family where hasuid='" + uid + "'";
		mDb.execSQL(del);
		for (int i = 0; i < myFamilylist.length(); i++) {
			JSONObject memberObj = myFamilylist.getJSONObject(i);
			String newsValueStr = "";
			boolean isAdd = true;
			for (String field : myFamilyField) {
				if (memberObj.has(field)) {
					String value = memberObj.getString(field);
					if (value != null) {
						if (!"".equals(newsValueStr))
							newsValueStr += ",";
						newsValueStr += "'" + Common.formatSqlValue(value) + "'";
					} else {
						isAdd = false;
						break;
					}
				} else {
					isAdd = false;
					break;
				}
			}
			if (isAdd) {
				String insertTwitter = "REPLACE INTO my_family('hasuid'," + Common.sImplode(myFamilyField) + ") VALUES ('" + uid + "'," + newsValueStr + ")";
				mDb.execSQL(insertTwitter);
			}
		}
		close();
	}

	/**
	 * 
	 * 更新一个成员信息
	 */
	public void updateMyFamily(int uid, JSONObject memberObj) throws JSONException {
		if (!Common.empty(memberObj)) {
			open();
			String newsValueStr = "";
			boolean isAdd = true;
			for (String field : myFamilyField) {
				if (memberObj.has(field)) {
					String value = memberObj.getString(field);
					if (value != null) {
						if (!"".equals(newsValueStr))
							newsValueStr += ",";
						newsValueStr += "'" + Common.formatSqlValue(value) + "'";
					} else {
						isAdd = false;
						break;
					}
				} else {
					isAdd = false;
					break;
				}
			}
			if (isAdd) {
				String insertTwitter = "REPLACE INTO my_family('hasuid'," + Common.sImplode(myFamilyField) + ") VALUES ('" + uid + "'," + newsValueStr + ")";
				mDb.execSQL(insertTwitter);
			}
			close();
		}
	}

	public List<MyFamilyMemberBean> loadMyFamily(int uid) {
		String sql = "SELECT * FROM my_family WHERE hasuid='" + uid + "'";
		List<MyFamilyMemberBean> list;
		list = executeQuery(sql, MyFamilyMemberBean.class);
		return list;
	}

	public MyFamilyMemberBean loadOneMyFamily(int uid, int gid) {
		String query = "SELECT * FROM my_family WHERE hasuid='" + uid + "' AND id='" + gid + "' limit 0,1";
		List<MyFamilyMemberBean> tmemberBeans = (List<MyFamilyMemberBean>) executeQuery(query, MyFamilyMemberBean.class);
		if (tmemberBeans.size() > 0) {
			return tmemberBeans.get(0);
		} else {
			return null;
		}
	}

	/*******************************************************
	 * 家庭成员管理end
	 ******************************************************/

	/*******************************************************
	 * 问候start
	 ******************************************************/
	/**
	 * 增加问候
	 * 
	 * @param uid
	 *            用户id
	 * @param jaList
	 *            收到的数据
	 * @param lasttime
	 *            加载的最后一条数据的创建数据，如果是刷新就是0
	 * @throws JSONException
	 */
	public void updateGreetingReceive(int uid, JSONArray jaList, int lasttime, String rtype, int senderid) throws JSONException {
		open();

		if (lasttime == 0) {
			String del = "DELETE FROM greeting_receive where hasuid='" + uid + "'AND rtype='" + rtype + "'AND sender='" + senderid + "'";
			mDb.execSQL(del);
		}

		for (int i = 0; i < jaList.length(); i++) {
			JSONObject dataObj = jaList.getJSONObject(i);
			if (!dataObj.has("author_innername")) {
				dataObj.put("author_innername", "");
			}
			if (!dataObj.has("receiver_innername")) {
				dataObj.put("receiver_innername", "");
			}

			String newsValueStr = "";
			boolean isAdd = true;
			for (String field : greetingField) {
				if (dataObj.has(field)) {
					String value = dataObj.getString(field);
					if (value != null) {
						if ("created_time".equals(field)) {
							value = Common.getChangeTime(value);
						}

						if ("sent_time".equals(field)) {
							value = Common.getChangeTime(value);
						}

						if (!"".equals(newsValueStr))
							newsValueStr += ",";
						newsValueStr += "'" + Common.formatSqlValue(value) + "'";
					} else {
						isAdd = false;
						break;
					}
				} else {
					isAdd = false;
					break;
				}
			}
			if (isAdd) {
				String insertData = "REPLACE INTO greeting_receive('hasuid','rtype','reply_by','sender'," + Common.sImplode(greetingField) + ") VALUES ('"
						+ uid + "','" + rtype + "','" + 0 + "','" + senderid + "'," + newsValueStr + ")";
				mDb.execSQL(insertData);

				try {
					if (!"S".equals(rtype)) {
						if (!Common.empty(dataObj.getJSONObject("reply_by"))) {
							updateReply(uid, dataObj.getJSONObject("reply_by"), rtype, 0, dataObj.getInt("id"), false);
						}
					}

				} catch (Exception e) {
					// TODO: handle exception
				}
			}
		}
		close();
	}

	public void updateReply(int uid, JSONObject dataObj, String rtype, int senderid, int reply_by, Boolean isopen) throws JSONException {
		try {

			if (isopen) {
				open();
			}
			if (!dataObj.has("author_innername")) {
				dataObj.put("author_innername", "");
			}
			if (!dataObj.has("receiver_innername")) {
				dataObj.put("receiver_innername", "");
			}

			String newsValueStr = "";
			boolean isAdd = true;
			for (String field : greetingField) {
				if (dataObj.has(field)) {
					String value = dataObj.getString(field);
					if (value != null) {
						if ("created_time".equals(field)) {
							value = Common.getChangeTime(value);
						}

						if ("sent_time".equals(field)) {
							value = Common.getChangeTime(value);
						}

						if (!"".equals(newsValueStr))
							newsValueStr += ",";
						newsValueStr += "'" + Common.formatSqlValue(value) + "'";
					} else {
						isAdd = false;
						break;
					}
				} else {
					isAdd = false;
					break;
				}
			}
			if (isAdd) {
				String insertData = "REPLACE INTO greeting_receive('hasuid','rtype','reply_by','sender'," + Common.sImplode(greetingField) + ") VALUES ('"
						+ uid + "','" + rtype + "','" + reply_by + "','" + senderid + "'," + newsValueStr + ")";
				mDb.execSQL(insertData);
			}
			if (isopen) {
				close();
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	/**
	 * 加载问候
	 * 
	 * @param uid
	 *            用户ID
	 * 
	 * @param dateline
	 *            时间段
	 * 
	 * @param rtype
	 *            问候数据类型, 取值: S-发送的问候, R-收到的问候
	 * 
	 */
	public List<GreetingNewsBean> loadGreetingReceive(int uid, int dateline, String rtype, int sender) {

		try {
			String dateSql = "", sql = "SELECT * FROM greeting_receive WHERE hasuid='" + uid + "'AND rtype='" + rtype + "'AND sender='" + sender + "'";
			if (dateline > 0) {
				dateSql = " AND sent_time<'" + dateline + "'";
			}
			sql = sql + dateSql + "ORDER BY sent_time DESC limit 0," + familynewsmaxitems;
			List<GreetingNewsBean> list;
			list = executeQuery(sql, GreetingNewsBean.class);

			for (int i = 0; i < list.size(); i++) {
				List<GreetingNewsBean> listReply = loadreply(uid, rtype, list.get(i).getId());
				if (!Common.empty(listReply)) {
					list.get(i).setGreetingNewsBean2(listReply.get(0));
				}
			}
			return list;

		} catch (Exception e) {
			return null;
		}

	}

	public List<GreetingNewsBean> loadreply(int uid, String rtype, int reply_by) {

		try {

			String dateSql = "", sql = "SELECT * FROM greeting_receive WHERE hasuid='" + uid + "'AND rtype='" + rtype + "'AND reply_by='" + reply_by
					+ "'AND sender='" + 0 + "'";
			List<GreetingNewsBean> list;
			list = executeQuery(sql, GreetingNewsBean.class);
			return list;

		} catch (Exception e) {
			return null;
			// TODO: handle exception
		}

	}

	/**
	 * 删除发送的问候
	 */
	public void deleteSendReceive(int uid, int id) {
		String sql = "DELETE FROM greeting_receive WHERE hasuid='" + uid + "' AND id='" + id + "'";
		open();
		mDb.execSQL(sql);
		close();
	}

	/*******************************************************
	 * 问候end
	 ******************************************************/

	/*******************************************************
	 * 提醒start
	 ******************************************************/
	public void updateRegardsTip(int uid, JSONArray jaList, int lasttime) throws JSONException {
		open();
		int is_f = 1;

		if (lasttime == 0) {
			is_f = 0;
			String del = "DELETE FROM regards_tip where hasuid='" + uid + "'";
			mDb.execSQL(del);
		}

		for (int i = 0; i < jaList.length(); i++) {
			JSONObject dataObj = jaList.getJSONObject(i);

			// 已触发且未读的提醒数据(简记为: TT)排在最前面, 其他提醒数据(简记为: OT).
			int is_order = 0;
			if ("true".equals(dataObj.getString("is_triggered")) && "false".equals(dataObj.getString("is_checked"))) {
				is_order = 1;
			}

			String newsValueStr = "";
			boolean isAdd = true;
			for (String field : regardsTipField) {
				if (dataObj.has(field)) {
					String value = dataObj.getString(field);
					if (value != null) {
						if ("is_triggered".equals(field)) {
							if ("true".equals(value)) {
								value = "1";
							} else {
								value = "0";
							}
						}

						if ("spec_date".equals(field)) {
							value = Common.getChangeTime(value);
						}

						if (!"".equals(newsValueStr))
							newsValueStr += ",";
						newsValueStr += "'" + Common.formatSqlValue(value) + "'";
					} else {
						isAdd = false;
						break;
					}
				} else {
					isAdd = false;
					break;
				}
			}
			if (isAdd) {
				String insertData = "REPLACE INTO regards_tip('hasuid','is_f','is_order'," + Common.sImplode(regardsTipField) + ") VALUES ('" + uid + "','"
						+ is_f + "','" + is_order + "'," + newsValueStr + ")";
				mDb.execSQL(insertData);
			}
		}
		close();
	}

	public List<RegardsTipBean> loadRegardsTip(int uid, int dateline) {

		try {
			int is_f = 1;
			if (dateline == 0) {
				is_f = 0;
			}
			String dateSql = "", sql = "SELECT * FROM regards_tip WHERE hasuid='" + uid + "'" + " AND is_f='" + is_f + "'";

			if (dateline > 0) {
				dateSql = " AND spec_date>'" + dateline + "'";
			}

			if (is_f == 0) {
				sql = sql + dateSql + "ORDER BY is_order DESC, spec_date ASC";
			} else {
				sql = sql + dateSql + "ORDER BY spec_date limit 0," + familynewsmaxitems;
			}

			List<RegardsTipBean> list = executeQuery(sql, RegardsTipBean.class);
			return list;

		} catch (Exception e) {
			return null;
			// TODO: handle exception
		}

	}

	/**
	 * 加载一个提醒
	 * 
	 * @param uid
	 *            用户ID
	 * @param id
	 *            动态ID
	 */
	public RegardsTipBean loadOneRegardsTip(int uid, int id) {
		String query = "SELECT * FROM regards_tip WHERE hasuid='" + uid + "' AND id='" + id + "' limit 0,1";
		List<RegardsTipBean> fBeans = (List<RegardsTipBean>) executeQuery(query, RegardsTipBean.class);
		if (fBeans.size() > 0) {
			return fBeans.get(0);
		} else {
			return null;
		}
	}

	/*******************************************************
	 * 提醒end
	 ******************************************************/

	/*******************************************************
	 * 消息start
	 ******************************************************/

	public void updateNewsReceive(int uid, JSONArray jaList, int lasttime, int news_type) throws JSONException {
		open();

		if (lasttime == 0) {
			String del = "DELETE FROM news_info where hasuid='" + uid + "' AND news_type='" + news_type + "'";
			;
			mDb.execSQL(del);
		}

		for (int i = 0; i < jaList.length(); i++) {
			JSONObject dataObj = jaList.getJSONObject(i);
			String newsValueStr = "";
			boolean isAdd = true;
			for (String field : NewsField) {
				if (dataObj.has(field)) {
					String value = dataObj.getString(field);
					if (value != null) {
						if ("created_time".equals(field)) {
							value = Common.getChangeTime(value);
						}

						if (!"".equals(newsValueStr))
							newsValueStr += ",";
						newsValueStr += "'" + Common.formatSqlValue(value) + "'";
					} else {
						isAdd = false;
						break;
					}
				} else {
					isAdd = false;
					break;
				}
			}
			if (isAdd) {
				String insertData = "REPLACE INTO news_info('hasuid','news_type'," + Common.sImplode(NewsField) + ") VALUES ('" + uid + "','" + news_type
						+ "'," + newsValueStr + ")";
				mDb.execSQL(insertData);
			}
		}
		close();
	}

	public List<NewsBean> loadNews(int uid, int t, int dateline) {
		try {
			String sql = "SELECT * FROM news_info WHERE hasuid='" + uid + "'";
			if (dateline > 0) {
				sql = sql + " AND created_time<'" + dateline + "'";
			}

			sql = sql + " AND news_type='" + t + "'";

			sql = sql + "ORDER BY created_time DESC limit 0," + familynewsmaxitems;
			List<NewsBean> list;
			list = executeQuery(sql, NewsBean.class);
			return list;
		} catch (Exception e) {
			return null;
		}
	}

	/*******************************************************
	 * 消息end
	 ******************************************************/

	/*******************************************************
	 * 消息中心start
	 ******************************************************/

	public void updateNewss(int uid, JSONArray jaList, int lasttime, TextView tv_date_tip_count, NewssBean TNewssCount) throws JSONException {
		open();

		if (lasttime == 0) {
			String del = "DELETE FROM newss_info where hasuid='" + uid + "'";
			mDb.execSQL(del);
		}

		for (int i = 0; i < jaList.length(); i++) {
			JSONObject dataObj = jaList.getJSONObject(i);

			if ("T".equals(dataObj.get("msg_type"))) {
				int msg_count = dataObj.getInt("msg_count");
				if (msg_count > 0) {
					tv_date_tip_count.setVisibility(View.VISIBLE);
					tv_date_tip_count.setText(msg_count + "");
				} else {
					tv_date_tip_count.setVisibility(View.GONE);
				}
				TNewssCount.setMsg_count(msg_count);
				TNewssCount.setSender(dataObj.getInt("sender"));
			} else {
				String newsValueStr = "";
				boolean isAdd = true;
				for (String field : newssField) {
					if (dataObj.has(field)) {
						String value = dataObj.getString(field);
						if (value != null) {
							if ("item_time".equals(field)) {
								value = Common.getChangeTime(value);
							}

							if (!"".equals(newsValueStr))
								newsValueStr += ",";
							newsValueStr += "'" + Common.formatSqlValue(value) + "'";
						} else {
							isAdd = false;
							break;
						}
					} else {
						isAdd = false;
						break;
					}
				}
				if (isAdd) {
					String insertData = "REPLACE INTO newss_info('hasuid'," + Common.sImplode(newssField) + ") VALUES ('" + uid + "'," + newsValueStr + ")";
					mDb.execSQL(insertData);
				}
			}

		}
		close();
	}

	public List<NewssBean> loadNewss(int uid, int dateline) {

		try {
			String dateSql = "", sql = "SELECT * FROM newss_info WHERE hasuid='" + uid + "'";
			if (dateline > 0) {
				dateSql = " AND item_time<'" + dateline + "'";
			}
			sql = sql + dateSql + "ORDER BY item_time DESC limit 0," + familynewsmaxitems;

			List<NewssBean> list = executeQuery(sql, NewssBean.class);
			return list;

		} catch (Exception e) {
			return null;
			// TODO: handle exception
		}

	}

	/*******************************************************
	 * 消息中心end
	 ******************************************************/

	/*******************************************************
	 * 系统消息start
	 ******************************************************/

	public void updateSystemNews(int uid, JSONArray jaList, int lasttime) throws JSONException {
		open();

		if (lasttime == 0) {
			String del = "DELETE FROM systemnews where hasuid='" + uid + "'";
			mDb.execSQL(del);
		}

		for (int i = 0; i < jaList.length(); i++) {
			JSONObject dataObj = jaList.getJSONObject(i);
			String newsValueStr = "";
			boolean isAdd = true;
			for (String field : systemNews) {
				if (dataObj.has(field)) {
					String value = dataObj.getString(field);
					if (value != null) {
						if ("created_time".equals(field)) {
							value = Common.getChangeTime(value);
						}

						if (!"".equals(newsValueStr))
							newsValueStr += ",";
						newsValueStr += "'" + Common.formatSqlValue(value) + "'";
					} else {
						isAdd = false;
						break;
					}
				} else {
					isAdd = false;
					break;
				}
			}
			if (isAdd) {
				String insertData = "REPLACE INTO systemnews('hasuid'," + Common.sImplode(systemNews) + ") VALUES ('" + uid + "'," + newsValueStr + ")";
				mDb.execSQL(insertData);
			}
		}
		close();
	}

	public List<SystemNewsBean> loadSystemNews(int uid, int dateline) {

		try {
			String dateSql = "", sql = "SELECT * FROM systemnews WHERE hasuid='" + uid + "'";
			if (dateline > 0) {
				dateSql = " AND item_time<'" + dateline + "'";
			}
			sql = sql + dateSql + "ORDER BY created_time DESC limit 0," + familynewsmaxitems;

			List<SystemNewsBean> list = executeQuery(sql, SystemNewsBean.class);
			return list;

		} catch (Exception e) {
			return null;
			// TODO: handle exception
		}

	}

	/*******************************************************
	 * 系统消息end
	 ******************************************************/

	public List<ChatMsgBean> queryBehindChatMsg(int hasuid, int targetuid, int dateline) {
		String dateStr = "";
		if (dateline != 0) {
			dateStr = " AND dateline>'" + dateline + "' ";
		} else
			return null;
		String sql = "SELECT * FROM spm_msg WHERE hasuid ='" + hasuid + "' AND targetuid='" + targetuid + "' " + dateStr + " ORDER BY dateline";
		List<ChatMsgBean> msgs = executeQuery(sql, ChatMsgBean.class);
		return msgs;
	}

	public List<ChatMsgBean> queryBeforeChatMsg(int hasuid, int targetuid, int dateline) {
		String dateStr = "";
		if (dateline != 0) {
			dateStr = " AND dateline<'" + dateline + "' ";
		}
		String sql = "SELECT * FROM spm_msg WHERE hasuid ='" + hasuid + "' AND targetuid='" + targetuid + "' " + dateStr + " ORDER BY dateline desc limit 0,20";
		List<ChatMsgBean> msgs = executeQuery(sql, ChatMsgBean.class);
		List<ChatMsgBean> msglist = new LinkedList<ChatMsgBean>();
		int i = msgs.size() - 1;
		for (; i >= 0; i--) {
			msglist.add(msgs.get(i));
		}
		return msglist;
	}

	public void saveSendChatMsg(int msgid, int hasuid, int touid, String tousername, String msg, int dateline) {
		open();
		tousername = Common.formatSqlValue(tousername);
		msg = Common.formatSqlValue(msg);
		String insertMsg = "REPLACE INTO spm_msg(" + Common.sImplode(msgSaveField) + ") VALUES ('" + msgid + "','" + Common.formatSqlValue(msg) + "','" + touid
				+ "','" + Common.formatSqlValue(tousername) + "','" + dateline + "','" + hasuid + "','1')";
		mDb.execSQL(insertMsg);
		close();
	}

	public void saveReceivedChatMsg(int hasuid, JSONObject onemsg) throws JSONException {
		open();
		String msgValueStr = "";
		boolean isValid = true;
		for (String field : msgGetField) {
			String value = onemsg.getString(field);
			if (value != null) {
				if (!"".equals(msgValueStr))
					msgValueStr += ",";
				msgValueStr += "'" + Common.formatSqlValue(value) + "'";
			} else {
				isValid = false;
				break;
			}

		}
		if (isValid) {
			msgValueStr += ",'" + hasuid + "','0'";
			String insertMsg = "REPLACE INTO spm_msg(" + Common.sImplode(msgSaveField) + ") VALUES (" + msgValueStr + ")";
			mDb.execSQL(insertMsg);
		}
		close();
	}

	public void saveReceivedChatMsg(int hasuid, JSONArray chatMsgs) throws JSONException {
		if (chatMsgs != null && chatMsgs.length() != 0) {
			for (int j = 0; j < chatMsgs.length(); j++) {
				JSONObject onemsg = chatMsgs.getJSONObject(j);
				saveReceivedChatMsg(hasuid, onemsg);
			}
		}

	}

	public int getUnreadMsgCount(int hasuid) {
		open();
		String sql = "SELECT unread FROM spm_notification WHERE hasuid='" + hasuid + "' AND unread>0";
		Cursor c = mDb.rawQuery(sql, null);
		int num = 0;
		while (c.moveToNext()) {
			num += c.getInt(c.getColumnIndex("unread"));
		}
		if (c != null) {
			c.close();
			c = null;
		}
		close();
		return num;
	}

	public void addSysNotification(int hasuid, String fromname, String type, int addunreadnum, String msg, int dateline) {
		open();
		type = Common.formatSqlValue(type);
		msg = Common.formatSqlValue(msg);
		fromname = Common.formatSqlValue(fromname);
		String sql = "SELECT unread FROM spm_notification where hasuid='" + hasuid + "' AND type='" + type + "' AND targetid='0'";
		Cursor c = mDb.rawQuery(sql, null);
		int num = 0;
		while (c.moveToNext()) {
			num += c.getInt(c.getColumnIndex("unread"));
		}
		num += addunreadnum;
		sql = "REPLACE INTO spm_notification(hasuid,targetid,targetname,type,lastmsg,dateline,unread)" + "VALUES('" + hasuid + "','" + 0 + "','" + fromname
				+ "','" + type + "','" + msg + "','" + dateline + "','" + num + "')";
		mDb.execSQL(sql);
		if (c != null) {
			c.close();
			c = null;
		}
		close();
	}

	public void addChatNotification(int hasuid, int targetuid, String targetname, int addunreadnum, String msg, int dateline) {
		open();
		String sql = "SELECT unread FROM spm_notification where hasuid='" + hasuid + "' AND type='" + UpdateManager.NOTIF_TYPE_CHAT + "' AND targetid='"
				+ targetuid + "'";
		Cursor c = mDb.rawQuery(sql, null);
		int num = 0;
		while (c.moveToNext()) {
			num += c.getInt(c.getColumnIndex("unread"));
		}
		num += addunreadnum;
		sql = "REPLACE INTO spm_notification(hasuid,targetid,targetname,type,lastmsg,dateline,unread)" + "VALUES('" + hasuid + "','" + targetuid + "','"
				+ Common.formatSqlValue(targetname) + "','" + UpdateManager.NOTIF_TYPE_CHAT + "','" + Common.formatSqlValue(msg) + "','" + dateline + "','"
				+ num + "')";
		mDb.execSQL(sql);
		if (c != null) {
			c.close();
			c = null;
		}
		close();
	}

	public Map<String, List<NotificationBean>> queryNotification(int hasuid) {
		String sql = "SELECT * FROM spm_notification WHERE hasuid='" + hasuid + "' AND targetid='" + 0 + "'";
		List<NotificationBean> sys = (List<NotificationBean>) executeQuery(sql, NotificationBean.class);
		sql = "SELECT * FROM spm_notification WHERE hasuid='" + hasuid + "' AND targetid>'" + 0 + "' ORDER BY dateline DESC";
		List<NotificationBean> chat = (List<NotificationBean>) executeQuery(sql, NotificationBean.class);
		Map<String, List<NotificationBean>> data = new HashMap<String, List<NotificationBean>>();
		data.put("sys", sys);
		data.put("chat", chat);
		return data;
	}

	public List<NotificationBean> querySysNotification(int hasuid) {
		String sql = "SELECT * FROM spm_notification WHERE hasuid='" + hasuid + "' AND targetid='" + 0 + "'";
		List<NotificationBean> sys = (List<NotificationBean>) executeQuery(sql, NotificationBean.class);
		return sys;
	}

	public void saveNotifyDetail(int hasuid, JSONArray notify) throws JSONException {
		if (notify != null && notify.length() != 0) {
			for (int j = 0; j < notify.length(); j++) {
				JSONObject onemsg = notify.getJSONObject(j);
				saveNotifyDetail(hasuid, onemsg);
			}
		}
	}

	public void saveNotifyDetail(int hasuid, JSONObject notify) throws JSONException {
		open();
		String msgValueStr = "";
		boolean isValid = true;
		for (String field : notifyGetField) {
			String value = notify.getString(field);
			if (value != null) {
				if (!"".equals(msgValueStr))
					msgValueStr += ",";
				msgValueStr += "'" + Common.formatSqlValue(value) + "'";
			} else {
				isValid = false;
				break;
			}

		}
		if (isValid) {
			msgValueStr = "'" + hasuid + "'," + msgValueStr;
			String insertMsg = "REPLACE INTO spm_notifydetail(" + Common.sImplode(notifySaveField) + ") VALUES (" + msgValueStr + ")";
			mDb.execSQL(insertMsg);
		}
		close();
	}

	public Map<String, Integer> checkNotifyLast(int hasuid) {
		String sql = "SELECT dateline FROM spm_notification WHERE hasuid='" + hasuid + "' AND targetid=0 ORDER BY dateline DESC limit 0,1";
		Map<String, Integer> data = new HashMap<String, Integer>();
		open();
		Cursor c = mDb.rawQuery(sql, null);
		int num = 0;
		while (c.moveToNext()) {
			num += c.getInt(c.getColumnIndex("dateline"));
		}
		if (num == 0)
			num = 1;
		data.put("sys", num);
		num = 0;
		sql = "SELECT dateline FROM spm_notification WHERE hasuid='" + hasuid + "' AND type='" + UpdateManager.NOTIF_TYPE_CHAT
				+ "' ORDER BY dateline DESC limit 0,1";
		c = mDb.rawQuery(sql, null);
		while (c.moveToNext()) {
			num += c.getInt(c.getColumnIndex("dateline"));
		}
		if (num == 0)
			num = 1;
		data.put("chat", num);
		if (c != null) {
			c.close();
			c = null;
		}
		close();
		return data;
	}

	public List<NotifyDetailBean> queryRelationNotification(int hasuid, int dateline) {
		String timeStr = "";
		if (dateline != 0) {
			timeStr = " AND dateline<'" + dateline + "' ";
		}
		String sql = "SELECT * FROM spm_notifydetail WHERE hasuid='" + hasuid + "' AND (type='" + UpdateManager.NOTIF_TYPE_ADD_FRIEND + "' OR type='"
				+ UpdateManager.NOTIF_TYPE_BE_FRIEND + "' OR type='" + UpdateManager.NOTIF_TYPE_BE_FANS + "') " + timeStr + " ORDER BY dateline DESC limit 0,"
				+ relationmaxitems;
		List<NotifyDetailBean> relation = (List<NotifyDetailBean>) executeQuery(sql, NotifyDetailBean.class);
		return relation;
	}

	public void delnotificationdetail(int hasuid, int nid) {
		String sql = "DELETE FROM spm_notifydetail WHERE hasuid='" + hasuid + "' AND nid='" + nid + "'";
		open();
		mDb.execSQL(sql);
		close();
	}

	public void delnotificationdetail(int hasuid, String type) {
		String typeStr = "";
		if ("relation".equals(type)) {
			typeStr = " AND (type='" + UpdateManager.NOTIF_TYPE_BE_FRIEND + "' OR type='" + UpdateManager.NOTIF_TYPE_BE_FANS + "') ";
		}
		String sql = "DELETE FROM spm_notifydetail WHERE hasuid='" + hasuid + "'" + typeStr;
		open();
		mDb.execSQL(sql);
		close();
	}

}
